package com.zygh.questionnairesurvey.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zygh.questionnairesurvey.entity.Problem;
import com.zygh.questionnairesurvey.entity.ProblemDetails;
import com.zygh.questionnairesurvey.entity.Questionnaire;
import com.zygh.questionnairesurvey.entity.UserInfo;
import com.zygh.questionnairesurvey.entity.vo.UserInfoVO;
import com.zygh.questionnairesurvey.entity.vo.UserVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.web.PageableDefault;

import java.util.List;

@Mapper
public interface UserInfoMapper extends BaseMapper<UserInfo> {

    @Select("SELECT u.*,d.`name` as deptName,r.`name` as roleName,(select name from dept t where t.dept_id=d.pid) as pName " +
            "FROM `user_info` u   " +
            "LEFT JOIN dept d ON u.dept_id=d.dept_id LEFT JOIN user_role r ON u.role_id = r.id " +
            "WHERE user_name=#{username} AND STATUS = 0")
    UserVO getUserInfo(String username);

    @Select("SELECT * FROM `user_info` WHERE user_name=#{username}")
    UserInfo findUserByUsername(String username);


    @Select("<script> SELECT u.id,u.name,u.user_name,u.dept_id,u.role_id,u.`status`,u.pass_status,u.createtime,u.term_status,u.is_superadmin,u.check_status" +
            ",d.`name` as deptName,(SELECT t.`name` FROM dept t WHERE t.dept_id = d.pid) as pName FROM user_info u LEFT JOIN dept d ON u.dept_id = d.dept_id " +
            "<where> is_superadmin=0" +
            "<if test=' warningsignal != null and warningsignal.length &gt; 0 ' > and u.user_name like concat('%',#{warningsignal},'%') </if> " +
            "<if test=' termStatus != null '> and u.term_status = #{termStatus} </if> " +
            "<if test=' name != null and name.length &gt; 0 '> and u.name like concat('%',#{name},'%') </if>" +
            "<if test='deptId != null'> and u.dept_id = #{deptId} </if>  </where> ORDER BY d.sort,u.id ASC </script>")
    List<UserInfo> findUserInfo(@Param("name") String name,
                                @Param("termStatus") Integer termStatus,
                                @Param("deptId") Integer deptId,
                                @Param("warningsignal") String warningsignal);

    @Select("<script> SELECT u.id,u.name,u.user_name,u.dept_id,u.role_id,u.`status`,u.pass_status,u.createtime,u.term_status,u.is_superadmin,u.check_status" +
            ",d.`name` as deptName,(SELECT t.`name` FROM dept t WHERE t.dept_id = d.pid) as pName FROM user_info u LEFT JOIN dept d ON u.dept_id = d.dept_id " +
            "<where> is_superadmin=0" +
            "<if test=' termStatus != null '> and u.term_status = #{termStatus} </if> " +
            "<if test='deptId != null'> and d.pid = #{deptId} </if>  </where> ORDER BY d.sort,u.id ASC </script>")
    List<UserInfo> findUserPidInfo(@Param("termStatus") Integer termStatus,
                                   @Param("deptId") Integer deptId);


    @Select("<script>SELECT u.id,u.name,u.user_name,u.dept_id,u.role_id,u.`status`,u.pass_status,u.createtime," +
            "(SELECT name  FROM dept WHERE dept_id= u.dept_id) as deptName," +
            "(SELECT name  FROM dept WHERE dept_id= d.pid) as pName " +
            "FROM questionnaire q RIGHT JOIN user_info u ON q.warningsignal  =  u.user_name LEFT JOIN dept d on u.dept_id = d.dept_id " +
            "WHERE q.warningsignal IS NULL <if test=\"param1 != null\"> AND FIND_IN_SET(d.dept_id,getChildrenOrgOfHr(#{param1})) </if>" +
            "<if test=\"param2 != null and param2 != ''\"> and u.user_name = #{param2} </if>" +
            "<if test=\"param3 != null and param3 != ''\"> and u.name like concat('%',#{param3},'%') </if>" +
            "and u.is_superadmin = 0 </script>")
    List<UserInfo> findUserByQuest(Integer unitId, String code, String name);

    @Select("SELECT u.id,u.name,u.user_name,(SELECT name FROM dept d WHERE d.dept_id=u.dept_id) as deptName " +
            "FROM user_info u WHERE u.user_name = #{username}")
    UserInfoVO findUserByName(String username);


    @Select("<script> SELECT q.id, q.name, q.dept_name, q.dept_id, q.warningsignal as user_name,q.update_time,(SELECT t.`name` FROM dept t WHERE t.dept_id = d.pid) as pName" +
            " FROM questionnaire q LEFT JOIN dept d ON q.dept_id = d.dept_id " +
            " <where>" +
            "     <if test='deptId != null  and deptId.length &gt; 0 '> d.dept_id = #{deptId} or d.pid =#{deptId} </if>" +
            "     <if test='warningsignal != null  and warningsignal.length &gt; 0 '>and q.warningsignal like concat('%',#{warningsignal},'%') </if>\n" +
            "     <if test='name != null  and name.length &gt; 0 '>and q.name like concat('%',#{name},'%') </if>\n" +
            " </where> </script>")
    List<UserInfoVO> findUserInfoById(@Param("deptId") String deptId,
                                      @Param("warningsignal") String warningsignal,
                                      @Param("name") String name);

    @Select("<script> " +
            "SELECT p.* FROM questionnaire  q LEFT JOIN problem p ON FIND_IN_SET(p.id,q.lowest) LEFT JOIN user_info u ON q.warningsignal = u.user_name " +
            "<where> " +
            "<if test='deptId != null and deptId.length &gt; 0 '> u.dept_id = #{deptId} </if>" +
            "<if test='warningsignal != null and warningsignal.length &gt; 0  '> and u.user_name = #{warningsignal} </if>" +
            " AND p.id not IN " +
            "(SELECT p.pid FROM questionnaire  q LEFT JOIN problem p ON FIND_IN_SET(p.id,q.lowest)  LEFT JOIN user_info u ON q.warningsignal = u.user_name " +
            "<where> " +
            "<if test='deptId != null and deptId.length &gt; 0 '> u.dept_id = #{deptId} </if>" +
            "<if test='warningsignal != null and warningsignal.length &gt; 0  '> and u.user_name = #{warningsignal} </if> " +
            "and p.rank!=1) </where> " +
            "</where> </script>")
    List<Problem> findCountById(@Param("deptId") String deptId,
                                @Param("warningsignal") String warningsignal);


    @Select("SELECT u.id,u.name,u.user_name,(SELECT name  FROM dept WHERE dept_id= u.dept_id) as deptName " +
            "FROM questionnaire q LEFT JOIN user_info u ON q.warningsignal = u.user_name " +
            "WHERE  FIND_IN_SET(#{id},q.lowest)")
    List<UserInfoVO> findUserByProbelm(Integer id);


    /**
     * 查询已填写警员
     *
     * @return 符合条件警员信息
     */
    @Select("<script>" +
            "SELECT " +
            "(CASE ui.term_status\n" +
            "WHEN 0 THEN " +
            " '民警' " +
            " WHEN 1 THEN " +
            " '领导'\n" +
            " WHEN 3 THEN\n" +
            " '工勤人员'\n" +
            " ELSE\n" +
            " ''\n" +
            "END\n" +
            ") as term_name\n" +
            ",\n" +
            "d.pid,ui.name, ui.user_name, d.name as dept_name,count(q.warningsignal)" +
            "FROM user_info ui\n" +
            "INNER JOIN dept d on ui.dept_id =d.dept_id \n" +
            "INNER JOIN questionnaire q on q.warningsignal = ui.user_name\n" +
            "WHERE 1 = 1 " +
            "<if test = 'deptId != null'>" +
            "AND FIND_IN_SET(d.dept_id,getChildrenOrgOfHr(#{deptId})) " +
            "</if>" +
            "<if test = 'termStatus != null'>" +
            "AND ui.term_status = #{termStatus} " +
            "</if> and ui.is_superadmin = 0 " +
            "GROUP BY ui.user_name ;" +
            "</script>")
    List<UserInfoVO> listByFillIn(@Param("deptId") Integer deptId,
                                  @Param("termStatus") Integer termStatus);

    /**
     * 查询未填写警员
     *
     * @return 符合条件警员信息
     */
    @Select("<script>" +
            "SELECT " +
            "(CASE x.term_status\n" +
            "WHEN 0 THEN " +
            " '民警' " +
            " WHEN 1 THEN " +
            " '领导'\n" +
            " WHEN 3 THEN\n" +
            " '工勤人员'\n" +
            " ELSE\n" +
            " ''\n" +
            "END\n" +
            ") as term_name\n" +
            ",\n" +
            "x.pid,x.user_name,x.name,x.dept_name FROM \n" +
            "(SELECT is_superadmin,d.pid,ui.dept_id,ui.name, ui.user_name,q.warningsignal,d.name AS dept_name,ui.term_status " +
            " FROM user_info ui \n" +
            "LEFT JOIN dept d ON d.dept_id = ui.dept_id\n" +
            "LEFT JOIN questionnaire q ON q.warningsignal = ui.user_name)\n" +
            "AS x WHERE x.warningsignal IS NULL " +
            "<if test = 'deptId != null'> " +
            "AND FIND_IN_SET(x.dept_id,getChildrenOrgOfHr(#{deptId})) " +
            "</if>" +
            "<if test = 'termStatus != null'>" +
            "AND x.term_status = #{termStatus} " +
            "</if> and x.is_superadmin = 0  " +
            "</script>")
    List<UserInfoVO> listByNoFillIn(@Param("deptId") Integer deptId,
                                    @Param("termStatus") Integer termStatus);

    /**
     * 查询已填写有问题警员
     *
     * @return 符合条件警员信息
     */
    @Select("<script>" +
            "SELECT " +
            "(CASE ui.term_status\n" +
            "WHEN 0 THEN " +
            " '民警' " +
            " WHEN 1 THEN " +
            " '领导'\n" +
            " WHEN 3 THEN\n" +
            " '工勤人员'\n" +
            " ELSE\n" +
            " ''\n" +
            "END\n" +
            ") as term_name\n" +
            ",\n" +
            "d.pid,ui.name, ui.user_name, d.name as dept_name,count(q.warningsignal) " +
            "FROM user_info ui\n" +
            "INNER JOIN dept d on ui.dept_id =d.dept_id \n" +
            "INNER JOIN questionnaire q on q.warningsignal = ui.user_name " +
            "AND LENGTH(q.lowest)-LENGTH(REPLACE(q.lowest,',',''))+1 IS NOT NULL \n" +
            "WHERE 1 = 1" +
            "<if test = 'deptId != null'>" +
            "AND FIND_IN_SET(d.dept_id,getChildrenOrgOfHr(#{deptId})) " +
            "</if>" +
            "<if test = 'termStatus != null'>" +
            "AND ui.term_status = #{termStatus} " +
            "</if> and ui.is_superadmin = 0 " +
            "GROUP BY ui.user_name ;" +
            "</script>")
    List<UserInfoVO> listByProblem(@Param("deptId") Integer deptId,
                                   @Param("termStatus") Integer termStatus);

    /**
     * 查询已填写无问题警员
     *
     * @return 符合条件警员信息
     */
    @Select("<script>" +
            "SELECT " +
            "(CASE ui.term_status\n" +
            "WHEN 0 THEN " +
            " '民警' " +
            " WHEN 1 THEN " +
            " '领导'\n" +
            " WHEN 3 THEN\n" +
            " '工勤人员'\n" +
            " ELSE\n" +
            " ''\n" +
            "END\n" +
            ") as term_name\n" +
            ",\n" +
            "d.pid,ui.name, ui.user_name, d.name as dept_name,count(q.warningsignal), " +
            "LENGTH(q.lowest)-LENGTH(REPLACE(q.lowest,',',''))+1 as count\n" +
            "FROM user_info ui\n" +
            "INNER JOIN dept d on ui.dept_id =d.dept_id \n" +
            "INNER JOIN questionnaire q on q.warningsignal = ui.user_name " +
            "AND LENGTH(q.lowest)-LENGTH(REPLACE(q.lowest,',',''))+1 &lt; 2 " +
            "WHERE 1 = 1" +
            "<if test = 'deptId != null'>" +
            "AND find_in_set(d.dept_id ,getChildrenOrgOfHr(#{deptId})) " +
            "</if>" +
            "<if test = 'termStatus != null'>" +
            "AND ui.term_status = #{termStatus} " +
            "</if> and ui.is_superadmin = 0 " +
            "GROUP BY ui.user_name ;" +
            "</script>")
    List<UserInfoVO> listByNoProblem(@Param("deptId") Integer deptId,
                                     @Param("termStatus") Integer termStatus);

    /**
     * 查询导出人员信息
     *
     * @param id         部门id
     * @param termStatus 是否为领导班子（0：民警  1：领导班子   2：工勤人员）
     * @return
     */
    @Select("<script> SELECT u.id,u.name,u.user_name,u.dept_id,u.role_id,u.`status`,u.pass_status,u.createtime,\n" +
            "(CASE u.term_status\n" +
            "WHEN 0 THEN " +
            " '民警' " +
            " WHEN 1 THEN " +
            " '领导'\n" +
            " WHEN 3 THEN\n" +
            " '工勤人员'\n" +
            " ELSE\n" +
            " ''\n" +
            "END\n" +
            ") as termName\n" +
            ",\n" +
            "u.is_superadmin,u.check_status,d.`name` as deptName,(SELECT t.`name` FROM dept t WHERE t.dept_id = d.pid) as pName " +
            "FROM  user_info u LEFT JOIN dept d ON u.dept_id = d.dept_id  \n" +
            "WHERE  is_superadmin=0 " +
            "<if test=\"param1 != null\"> and  u.dept_id = #{param1}  OR d.pid=#{param1} </if> " +
            "<if test=\"param2 != null\"> and u.term_status=#{param2} </if>" +
            "</script>")
    List<UserInfo> findUserInfoByType(Integer id, Integer termStatus);

    /**
     * 根据部门id 分别统计该部门的民警，领导，工勤人员各多少人
     *
     * @param id 部门id
     * @return
     */
    @Select("<script> SELECT COUNT(*)  FROM user_info u LEFT JOIN dept d ON u.dept_id = d.dept_id " +
            "WHERE u.is_superadmin=0 and u.term_status = 0 <if test='id != null'> and d.dept_id = #{id} or (d.pid = #{id} AND u.term_status = 0) </if> union All \n" +
            "SELECT COUNT(*) FROM user_info u LEFT JOIN dept d ON u.dept_id = d.dept_id " +
            "WHERE u.is_superadmin=0 and u.term_status = 1 <if test='id != null'> and d.dept_id = #{id} or (d.pid = #{id} AND u.term_status = 1) </if> union All \n" +
            "SELECT COUNT(*) FROM user_info u LEFT JOIN dept d ON u.dept_id = d.dept_id " +
            "WHERE  u.is_superadmin=0 and u.term_status = 3 <if test='id != null'> and d.dept_id = #{id} or (d.pid = #{id} AND u.term_status = 3) </if> </script>")
    List<Integer> findUserCountByid(Integer id);


    @Select("<script> SELECT COUNT(*) FROM user_info u left JOIN dept d on u.dept_id = d.dept_id\n" +
            "<where> u.is_superadmin = 0 <if test='id != null'> and FIND_IN_SET(d.dept_id,getChildrenOrgOfHr(#{id})) </if>" +
            "</where> </script> ")
    int findUserCount(Integer id);

    @Select("<script> SELECT COUNT(*) FROM questionnaire q LEFT JOIN user_info u on q.warningsignal=u.user_name left JOIN dept d on u.dept_id = d.dept_id \n" +
            "<where> u.is_superadmin = 0 <if test='id != null'> and FIND_IN_SET(d.dept_id,getChildrenOrgOfHr(#{id})) </if>" +
            " </where> </script> ")
    int findUserNum(Integer id);

    @Select("<script>" +
            "SELECT DISTINCT ui.user_name FROM user_info ui \n" +
            "WHERE ui.dept_id IN " +
            "<foreach item='deptId' index='index' collection='list' open='(' separator=',' close=')'>" +
            "#{deptId}" +
            "</foreach>" +
            "</script>")
    List<String> listUserName(List<Integer> deptId);


    @Select("<script> " +
            "SELECT u.dept_id,u.user_name as warningsignal,m.encoder,u.`name`,m.name as problemName,p.details," +
            "(SELECT `name` FROM dept t WHERE t.dept_id = d.dept_id) as deptName," +
            "(SELECT `name` FROM dept t WHERE t.dept_id = d.pid) as pName" +
            " FROM problem_details p " +
            "LEFT JOIN problem m ON p.problem_id =  m.id " +
            "LEFT JOIN user_info u ON p.warningsignal = u.user_name LEFT JOIN dept d ON u.dept_id = d.dept_id\n" +
            "<where> " +
            "<if test = 'deptId != null'> FIND_IN_SET(d.dept_id,getChildrenOrgOfHr(#{deptId})) </if>  " +
            "<if test = 'encoder != null and encoder.length &gt; 0'> AND m.encoder like  concat('',#{encoder},'%') </if>  " +
            "<if test = 'termStatus != null and termStatus == 3 '> and u.term_status = #{termStatus} </if>  " +
            "<if test = 'termStatus != null and termStatus != 3 '> and u.term_status != 3 </if>  " +
            "<if test = 'warningsignal != null and warningsignal.length &gt; 0'> and u.user_name like concat('',#{warningsignal},'%') </if>  " +
            "<if test = 'name != null and name.length &gt; 0'> and u.name like concat('',#{name},'%') </if>  " +
            "</where> order by d.sort ASC </script>")
    List<ProblemDetails> finUserByDetails(@Param("deptId") Integer deptId,
                                          @Param("encoder") String encoder,
                                          @Param("termStatus") Integer termStatus,
                                          @Param("warningsignal") String warningsignal,
                                          @Param("name") String name);


    /**
     * 根据部门查询该部门已经填写的人员警号
     *
     * @param id
     * @return
     */
    @Select("<script> SELECT q.warningsignal as userName FROM questionnaire q LEFT JOIN user_info u ON q.warningsignal = u.user_name " +
            "LEFT JOIN dept d ON u.dept_id = d.dept_id  WHERE  u.is_superadmin = 0 \n" +
            "<if test = 'id != null'> and FIND_IN_SET( d.dept_id,getChildrenOrgOfHr(#{id})) </if> " +
            "<if test = 'termStatus != null and termStatus == 3 '> and u.term_status = #{termStatus} </if> " +
            "<if test = 'termStatus != null and termStatus !=3 '> and u.term_status != 3 </if> " +
            "</script>")
    List<UserInfo> findUserByDeptId(@Param("id") Integer id,
                                         @Param("termStatus") Integer termStatus);

}